package com.gyh.modules.excel;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.ParseException;
import java.util.Date;

import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
* @author 作者 gyh:
* @version 创建时间：2018年4月1日 下午7:56:36
* 
*/
public class ExcelUtils {
	
	public static Comment getComment(XSSFWorkbook wb,XSSFSheet sheet,String message) {
		CreationHelper factory = wb.getCreationHelper();
		ClientAnchor anchor = wb.getCreationHelper().createClientAnchor();
		RichTextString str = factory.createRichTextString(message);
		Comment comment = sheet.createDrawingPatriarch().createCellComment(anchor);
		comment.setString(str);
		return comment;
	}
	
	
	
	private static String[] parsePatterns = {
            "yyyy-MM-dd", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm", "yyyy-MM",
            "yyyy/MM/dd", "yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm", "yyyy/MM",
            "yyyy.MM.dd", "yyyy.MM.dd HH:mm:ss", "yyyy.MM.dd HH:mm", "yyyy.MM"};
	 /**
     * Utility: 获取workbook
     * @param path 路径
     * @return
	 * @throws ExcelException 
     */
	public static XSSFWorkbook getWorkbook(String path) throws ExcelException {
        XSSFWorkbook wb = null;
        try {
        	wb = new XSSFWorkbook(new FileInputStream(path));
        } catch (IOException e) {
            throw new ExcelException("文件读取失败");
        }
        return wb;
    }

    /**
     * 获取XSSFSheet
     * @return
     * @throws ExcelException 
     */
    public static XSSFSheet getSheet(XSSFWorkbook wb,int index) throws ExcelException {
    	if (wb == null) throw new ExcelException("找不到文件中的内容");
    	XSSFSheet sheet = wb.getSheetAt(index);
    	if (sheet == null)  throw new ExcelException("找不到文件中的内容");
    	return sheet;
    }
    
    
    /**
     * 判断本行时候为空
     * @param row
     * @return
     */
    public static boolean isRowEmpty(XSSFRow row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
                return false;
        }
        return true;
    }
    
    /**
     * 调用该方法，如果单元格为空 将抛出错误
     * @param cell
     * @return
     * @throws ExcelException
     */
    public static ExcelUtils nullOfError(Cell cell) throws ExcelException {
    	if (cell == null) throw new ExcelException("此单元格不能为空",cell); 
    	return new ExcelUtils();
    }
    
    
	/**
	 * 使用String类型接收cell中的数据
	 * @param cell
	 * @return
	 */
	public static String cellToString(Cell cell) {
		if (cell == null) return "";
		String result = "";
    	switch (cell.getCellTypeEnum()) {
		case STRING:
			result = cell.getStringCellValue();
			break;
		case NUMERIC:
			result = String.valueOf(cell.getNumericCellValue());
			break;
		case BLANK:
			result = "";
            break;
		default:  
	        break;  
		}
    	return result;
	}
	
	/**
	 * 使用double接收Cell数据,如果为空默认为 0
	 * @param cell
	 * @return
	 * @throws ExcelException 
	 */
	public static double cellToNumber(Cell cell) throws ExcelException {
		double result = 0;
		if (cell == null) return result;
    	switch (cell.getCellTypeEnum()) {
		case STRING:
			String paramet = cell.getStringCellValue();
			if (!paramet.matches("^(-?\\d+)(\\.\\d+)?$")) throw new ExcelException("数字类型不能存在特殊字符",cell);
			result = Double.parseDouble(cell.getStringCellValue());
			break;
		case NUMERIC:
			result = cell.getNumericCellValue();
			break;
		case BLANK:
			result = 0;
            break;
		default:  
	        break;  
		}
    	return result;
	}
	
	/**
	 * 使用double接收Cell数据
	 * @param cell
	 * @return
	 * @throws ExcelException 
	 */
	public static Double cellToNumberByNull(Cell cell) throws ExcelException {
		if (cell == null) return null;
		Double result = new Double(0);
    	switch (cell.getCellTypeEnum()) {
		case STRING:
			String paramet = cell.getStringCellValue();
			if (!paramet.matches("^(-?\\d+)(\\.\\d+)?$")) throw new ExcelException("数字类型中好友特殊字符",cell);
			result = Double.parseDouble(cell.getStringCellValue());
			break;
		case NUMERIC:
			result = cell.getNumericCellValue();
			break;
		case BLANK:
            break;
		default:  
	        break;  
		}
    	return result;
	}
	
	/**
	 * 使用Date类型接收Cell数据
	 * @param cell
	 * @return
	 * @throws ExcelException 
	 * @throws ParseException 
	 */
	public static Date cellToDate(Cell cell) throws ParseException, ExcelException {
		if (cell == null) return null;
		Date date = new Date();
    	switch (cell.getCellTypeEnum()) {
    	case NUMERIC:
    		if (DateUtil.isCellDateFormatted(cell)) {
    			date = cell.getDateCellValue();
    		} else {
    			throw new ExcelException("日期格式输入有误",cell);
    		}
			break;
    	case STRING:
    		String paramet = cell.getStringCellValue();
			if ("^(^(\\d{4}|\\d{2})(\\-|\\/|\\.)\\d{1,2}\\3\\d{1,2}$)|(^\\d{4}年\\d{1,2}月\\d{1,2}日$)$".matches(paramet)) {
				date = DateUtils.parseDate(paramet,parsePatterns);
			} else {
				throw new ExcelException("日期格式输入有误",cell);
			}
			break;
		case BLANK:
            break;
		default:  
			
	        break;  
		}
    	return date;
	}
	
	
	
	public static Date parseDate(Cell cell) throws ParseException {
		if (cell == null) {
			return null;
		}
		if (cell.getCellType() == 0) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			}
		} else {
			return DateUtils.parseDate(cell.getStringCellValue());
		}
		return null;
    }
}
